package tom.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import tom.client.invoice.Service;
import tom.shared.ContDept;
import tom.shared.Contact;
import tom.shared.ContactGroup;
import tom.shared.Customer;
import tom.shared.InvItem;
import tom.shared.Invoice;
import tom.shared.Payment;
import tom.shared.Paymeth;
import tom.shared.Product;
import tom.shared.RReason;
import tom.shared.Stock;
import tom.shared.Supplier;

public class SaleServiceImpl extends SessionService implements Service {

	private static final long serialVersionUID = -8441486422501703954L;

	@Override
	public Contact selectContact(String contCode) {
		String sql = "select * from contact where comCode='"
				+ super.getSessVar("comCode") + "' and contCode='" + contCode
				+ "'";
		ResultSet res = getDatabase().executeQuery(sql);
		Contact record = null;
		try {
			if (res.next()) {
				record = new Contact(res.getString(1), res.getString(2),
						res.getString(3), res.getString(4), res.getString(5),
						res.getString(6), res.getString(7), res.getString(8),
						res.getString(9), res.getString(10), res.getString(11),
						res.getString(12), res.getString(13),
						res.getString(14), res.getString(15),
						res.getString(16), res.getString(17),
						res.getString(18), res.getBoolean(19),
						res.getString(20), res.getString(21),
						res.getString(22), res.getTimestamp(23),
						res.getString(24), res.getTimestamp(25));
				record.custLimit = res.getDouble("custLimit");
				record.custBalance = res.getDouble("custBalance");
				record.custStatus = res.getString("custStatus");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return record;
	}

	private String getNextContCode() {
		ResultSet rs = super.getDatabase().executeQuery(
				"select contactNr from company where comCode='"
						+ super.getSessVar("comCode") + "' for update");
		Integer lastCode = 1;
		String nextCode = "000001";
		try {
			if (rs.next()) {
				lastCode = rs.getInt(1) + 1;
				if (lastCode != null)
					nextCode = String.format("%06d", lastCode);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		getDatabase().executeUpdate(
				"update company set contactNr=" + lastCode + " where comCode='"
						+ super.getSessVar("comCode") + "'");
		return nextCode;
	}

	@Override
	public boolean insertContact(Contact record) {
		record.comCode = this.getSessVar("comCode");
		record.creUser = this.getSessVar("user");
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("insert into contact (comCode,contCode,prefix,name,surname,taxId"
							+ "organiz,branch, address,addLine, tambon, "
							+ "amphur,province,postCode,phone,fax,mobile,email,active,note,label,creUser,creTime)"
							+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now())");

			if (record.contCode == null || record.contCode.equals("")) {
				record.contCode = getNextContCode();
			}

			stmt.setString(1, record.comCode);
			stmt.setString(2, record.contCode);
			stmt.setString(3, record.prefix);
			stmt.setString(4, record.name);
			stmt.setString(5, record.surname);
			stmt.setString(6, record.taxId);
			stmt.setString(7, record.organiz);
			stmt.setString(8, record.branch);
			stmt.setString(9, record.address);
			stmt.setString(10, record.addLine);
			stmt.setString(11, record.tambon);
			stmt.setString(12, record.amphur);
			stmt.setString(13, record.province);
			stmt.setString(14, record.postCode);
			stmt.setString(15, record.phone);
			stmt.setString(16, record.fax);
			stmt.setString(17, record.mobile);
			stmt.setString(18, record.email);
			stmt.setBoolean(19, record.active);
			stmt.setString(20, record.note);
			stmt.setString(21, record.label);
			stmt.setString(22, record.creUser);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updateContact(Contact record) {
		record.comCode = this.getSessVar("comCode");
		record.creUser = this.getSessVar("user");
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("update contact set prefix=?,name=?,surname=?,taxId=?,organiz=?,"
							+ "branch=?,address=?,addLine=?,tambon=?,amphur=?,province=?,postCode=?,phone=?,fax=?,"
							+ "mobile=?,email=?,active=?,note=?,label=?,modUser=?,modTime=NOW()"
							+ " where comCode=? and contCode=?");
			stmt.setString(1, record.prefix);
			stmt.setString(2, record.name);
			stmt.setString(3, record.surname);
			stmt.setString(4, record.taxId);
			stmt.setString(5, record.organiz);
			stmt.setString(6, record.branch);
			stmt.setString(7, record.address);
			stmt.setString(8, record.addLine);
			stmt.setString(9, record.tambon);
			stmt.setString(10, record.amphur);
			stmt.setString(11, record.province);
			stmt.setString(12, record.postCode);
			stmt.setString(13, record.phone);
			stmt.setString(14, record.fax);
			stmt.setString(15, record.mobile);
			stmt.setString(16, record.email);
			stmt.setBoolean(17, record.active);
			stmt.setString(18, record.note);
			stmt.setString(19, record.label);
			stmt.setString(20, record.modUser);
			stmt.setString(21, record.comCode);
			stmt.setString(22, record.contCode);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean deleteContact(String contCode) {
		int rowEffect = 0;
		try {
			rowEffect = getDatabase().executeUpdate(
					"delete from contact where comCode='"
							+ super.getSessVar("comCode") + "' and contCode='"
							+ contCode + "'");
		} catch (Exception e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public List<ContactGroup> selectContGroup(String contCode) {
		List<ContactGroup> list = new ArrayList<ContactGroup>();
		ResultSet rs = getDatabase().executeQuery(
				"select contactgroup.grpId, grpName from contactgroup, contactgrping"
						+ " where contactgrping.comCode='"
						+ getSessVar("comCode")
						+ "' and contactgrping.contCode='" + contCode + "'"
						+ " and contactgroup.comCode=contactgrping.comCode"
						+ " and contactgroup.grpId=contactgrping.grpId");
		try {
			ContactGroup grp;
			while (rs.next()) {
				grp = new ContactGroup();
				grp.grpId = rs.getInt(1);
				grp.grpName = rs.getString(2);
				list.add(grp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean insertContGroup(String contCode, Integer grpId) {
		int rowEffect = getDatabase().executeUpdate(
				"insert into contactgrping values('" + getSessVar("comCode")
						+ "','" + contCode + "'," + grpId + ")");
		return rowEffect > 0;
	}

	@Override
	public boolean deleteContGroup(String contCode, Integer grpId) {
		int rowEffect = getDatabase().executeUpdate(
				"delete from contactgrping where comCode='"
						+ getSessVar("comCode") + "' and contCode='" + contCode
						+ "' and grpId=" + grpId);
		return rowEffect > 0;
	}

	@Override
	public ContactGroup selectContGroup(Integer grpId) {
		ResultSet rs = getDatabase().executeQuery(
				"select * from contactgroup where comCode='"
						+ getSessVar("comCode") + "' and grpId=" + grpId);
		ContactGroup record = null;
		try {
			if (rs.next()) {
				record = new ContactGroup();
				record.grpId = rs.getInt(2);
				record.grpName = rs.getString(3);
				record.note = rs.getString(4);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return record;
	}

	@Override
	public boolean insertContGroup(ContactGroup record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("insert into contactgroup values(?, ?, ?, ?)");
			stmt.setString(1, getSessVar("comCode"));
			stmt.setInt(2, record.grpId);
			stmt.setString(3, record.grpName);
			stmt.setString(4, record.note);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowEffect > 0;
	}

	@Override
	public boolean deleteContGroup(Integer grpId) {
		int rowEffect = getDatabase().executeUpdate(
				"delete from contactgroup where comCode='"
						+ getSessVar("comCode") + "' and grpId=" + grpId);
		return rowEffect > 0;
	}

	@Override
	public boolean updateContGroup(ContactGroup record) {
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("update contactgroup set grpName=?, note=? where comCode=? and grpId=?");
			stmt.setString(1, record.grpName);
			stmt.setString(2, record.note);
			stmt.setString(3, getSessVar("comCode"));
			stmt.setInt(4, record.grpId);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rowEffect > 0;
	}

	@Override
	public Customer selectCustomer(String contCode) {
		String sql = "select * from contact where comCode='"
				+ super.getSessVar("comCode") + "' and contCode='" + contCode
				+ "'";
		ResultSet res = getDatabase().executeQuery(sql);
		Customer record = null;
		try {
			if (res.next()) {
				record = new Customer(res.getString("contCode"),
						res.getDouble("custLimit"),
						res.getDouble("custBalance"),
						res.getString("custVatCode"), res.getInt("custDueDay"),
						res.getString("custStatus"), res.getInt("custPayMeth"),
						res.getDate("custLastDate"),
						res.getString("custLastDocRef"),
						res.getInt("custLastInv"), res.getString("custLabel"),
						res.getString("modUser"), res.getDate("modTime"));
				record.contact = selectContact(contCode);
			}
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return record;
	}

	@Override
	public Supplier selectSupplier(String contCode) {
		String sql = "select * from contact where comCode='"
				+ super.getSessVar("comCode") + "' and contCode='" + contCode
				+ "'";
		ResultSet res = getDatabase().executeQuery(sql);
		Supplier record = null;
		try {
			if (res.next())
				record = new Supplier(res.getString("contCode"),
						res.getDouble("suppLimit"),
						res.getDouble("suppBalance"),
						res.getString("suppVatCode"), res.getInt("suppDueDay"),
						res.getString("suppStatus"), res.getInt("suppPayMeth"),
						res.getDate("suppLastDate"),
						res.getString("suppLastDocRef"),
						res.getInt("suppLastInv"), res.getString("suppLabel"),
						res.getString("modUser"), res.getDate("modTime"));
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return record;
	}

	@Override
	public boolean insertDebter(Customer record) {
		record.modUser = this.getSessVar("user");
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("insert into contact (comCode, contCode, debtLimit,"
							+ "vat,duePeriod,paymentMethod,creUser)"
							+ " values (?,?,?,?,?,?,?)");

			stmt.setString(1, this.getSessVar("comCode"));
			stmt.setString(2, record.contCode);
			stmt.setObject(3, record.custLimit);
			stmt.setObject(4, record.custVatCode);
			stmt.setObject(5, record.custDueDay);
			stmt.setObject(6, record.custPayMeth);
			stmt.setObject(7, record.modUser);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updateCustomer(Customer record) {
		// record.comCode = this.getSessVar("comCode");
		record.modUser = this.getSessVar("user");
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("update contact set custLimit=?,custBalance=?,"
							+ "custVatCode=?,custDueDay=?,custStatus=?,custPayMeth=?,custLastDate=?,"
							+ "custLastDocRef=?,custLastInv=?,custLabel=?,modUser=?,modTime=now()"
							+ " where comCode=? and contCode=?");
			stmt.setObject(1, record.custLimit);
			stmt.setObject(2, record.custBalance);
			stmt.setObject(3, record.custVatCode);
			stmt.setObject(4, record.custDueDay);
			stmt.setString(5, record.custStatus);
			stmt.setObject(6, record.custPayMeth);
			stmt.setObject(7, record.custLastDate);
			stmt.setString(8, record.custLastDocRef);
			stmt.setObject(9, record.custLastInv);
			stmt.setObject(10, record.custLabel);
			stmt.setString(11, record.modUser);
			stmt.setString(12, this.getSessVar("comCode"));
			stmt.setString(13, record.contCode);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean updateSupplier(Supplier record) {
		// record.comCode = this.getSessVar("comCode");
		record.modUser = this.getSessVar("user");
		Connection conn = super.getDatabase().getConnection();
		PreparedStatement stmt;
		int rowEffect = 0;
		try {
			stmt = conn
					.prepareStatement("update contact set suppLimit=?,suppBalance=?,"
							+ "suppVatCode=?,suppDueDay=?,suppStatus=?,suppPayMeth=?,suppLastDate=?,"
							+ "suppLastDocRef=?,suppLastInv=?,suppLabel=?,modUser=?,modTime=now()"
							+ " where comCode=? and contCode=?");
			stmt.setObject(1, record.suppLimit);
			stmt.setObject(2, record.suppBalance);
			stmt.setObject(3, record.suppVatCode);
			stmt.setObject(4, record.suppDueDay);
			stmt.setString(5, record.suppStatus);
			stmt.setObject(6, record.suppPayMeth);
			stmt.setObject(7, record.suppLastDate);
			stmt.setString(8, record.suppLastDocRef);
			stmt.setObject(9, record.suppLastInv);
			stmt.setObject(10, record.suppLabel);
			stmt.setString(11, record.modUser);
			stmt.setString(12, this.getSessVar("comCode"));
			stmt.setString(13, record.contCode);
			rowEffect = stmt.executeUpdate();
		} catch (SQLException e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public boolean deleteDebter(String contCode) {
		int rowEffect = 0;
		try {
			rowEffect = getDatabase().executeUpdate(
					"delete from debter where comCode='"
							+ super.getSessVar("comCode") + "' and contCode='"
							+ contCode + "'");
		} catch (Exception e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public String insertInvoice(Invoice inv) {
		String invNo = null;
		Database db = getDatabase();
		// System.out.println("Payment : " + inv.payments.size());

		db.createSQL(
				"insInv",
				"insert into invoice(comCode,invNo,invDate,systemKind,docuKind,docRef,"
						+ "contCode,totalAmt,totalAmtText,contactAddr,totalVat, shipDate, dueDate, creUser, creTime, totalPaid,status,refundCode,refundSource,totalItem,note,shipTo,spcDeduct,poNr) "
						+ "values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?, NOW(), ?, '0',?,?,?,?,?,?,?)");
		db.createSQL(
				"insInvItem",
				"insert into invitem(comCode,invNo,itemNo,proCode,proName,qty,unitPrice,vat,vatAmt,discount,total,stkCode, creUser, creTime, refundPrc, refundQty, refundTotal, refundItem, unit) "
						+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?,?,?,?)");
		Object[] params = new Object[22];
		try {
			db.executeUpdate("start transaction");
			ResultSet rs = db
					.executeQuery("select invoiceNr from company where comCode='"
							+ getSessVar("comCode") + "'");
			if (rs.next()) {
				inv.invNo = rs.getInt(1);
				if (inv.invNo == null)
					inv.invNo = 1;
				else
					inv.invNo++;
			}

			if (inv.docRef == null || inv.docRef.equals(""))
				inv.docRef = createTempDocRef(inv.invNo);

			inv.invDate = new Date();
			params[0] = getSessVar("comCode");
			params[1] = inv.invNo;
			params[2] = inv.invDate;
			params[3] = inv.systemKind;
			params[4] = inv.docuKind;
			params[5] = inv.docRef;
			params[6] = inv.contCode;
			params[7] = inv.totalAmt;
			params[8] = inv.totalAmtText;
			params[9] = inv.contactAddr;
			params[10] = inv.totalVat;
			params[11] = inv.shipDate;
			params[12] = inv.dueDate;
			params[13] = getSessVar("user");
			params[14] = inv.totalPaid;
			params[15] = inv.refundCode;
			params[16] = inv.refundSource;
			params[17] = inv.totalItem;
			params[18] = inv.note;
			params[19] = inv.shipTo;
			params[20] = inv.spcDeduct;
			params[21] = inv.poNr;
			db.execSqlMap("insInv", params);

			params = new Object[18];
			params[0] = getSessVar("comCode");
			params[1] = inv.invNo;

			for (InvItem item : inv.items) {
				item.invNo = inv.invNo;
				insertInvItem(inv.invNo, params, item);
			}
			// this.updateStock(inv);
			db.executeUpdate("update company set invoiceNr=invoiceNr+1 where comCode='"
					+ getSessVar("comCode") + "'");

			// db.createSQL("insPayment", SQLLib.getString("insertPayment"));
			// params = new Object[30];

			// db.execSqlMap("insPayment", params);

			inv.status = "0";
			if (inv.upload == 1)
				insertPayment(inv);
			db.executeUpdate("commit");
			invNo = inv.invNo.toString();

		} catch (Exception e) {
			System.out.println("ERROR!!" + e.getMessage());
			getDatabase().executeUpdate("rollback");
			invNo = e.getMessage();
		}
		return invNo;
	}

	public void insertInvItem(Integer invNo, Object[] params, InvItem item)
			throws SQLException {
		Database db = getDatabase();
		ResultSet rs = db
				.executeQuery("select itemNr from company where comCode='"
						+ getSessVar("comCode") + "'");
		if (rs.next()) {
			item.item = rs.getInt(1);
			if (item.item == null)
				item.item = 1;
			else
				item.item++;
		}
		params[0] = getSessVar("comCode");
		params[1] = invNo;
		params[2] = item.item;
		params[3] = item.proCode;
		params[4] = item.proName;
		params[5] = item.qty;
		params[6] = item.unitPrice;
		params[7] = item.vat;
		params[8] = item.vatAmt;
		params[9] = item.discount;
		params[10] = item.total;
		params[11] = item.stkCode;
		params[12] = getSessVar("user");
		params[13] = item.refundPrc;
		params[14] = item.refundQty;
		params[15] = item.refundTotal;
		params[16] = item.refundItem;
		params[17] = item.unit;
		db.execSqlMap("insInvItem", params);
		db.executeUpdate("update company set itemNr=itemNr+1 where comCode='"
				+ getSessVar("comCode") + "'");
	}

	private String createTempDocRef(Integer invNo) {
		return "T." + formatDocRef(invNo);
	}

	private String formatDocRef(Integer invNo) {
		return String.format("%03d-%04d", invNo / 10000, invNo % 10000);
	}

	@Override
	public List<Invoice> searchInvoice(String docRef, String invDate,
			String docuKind, String systemKind, String totalAmt,
			Boolean balanceDue, String address, String contCode, String status) {
		String sql = "select * from invoice where comCode='"
				+ super.getSessVar("comCode") + "' ";
		if (docRef == null || docRef.equals(""))
			docRef = "%";
		sql = sql + " and docRef like '" + docRef + "'";

		if (invDate != null) {
			sql = sql + " and date(invDate) ='" + invDate + "'";
		}
		if (docuKind != null) {
			sql = sql + " and docuKind ='" + docuKind + "'";
		}
		if (systemKind != null) {
			sql = sql + " and systemKind ='" + systemKind + "'";
		}
		if (totalAmt != null) {
			sql = sql + " and totalAmt " + totalAmt;
		}
		if (balanceDue) {
			sql = sql + " and totalAmt != totalPaid and pendingReceipt=0";
		}
		if (address != null && !address.equals("")) {
			sql = sql + " and contAddr like '" + address + "'";
		}
		if (contCode != null && !contCode.equals("")) {
			sql = sql + " and contCode ='" + contCode + "'";
		}

		if (status != null && !status.equals("")) {
			sql = sql + " and status >='" + status + "'";
		}
		sql = sql + " order by invNo desc";
		System.out.println(sql);
		List<Invoice> list = new ArrayList<Invoice>();
		ResultSet res = getDatabase().executeQuery(sql);
		Invoice record = null;
		try {
			while (res.next()) {
				record = createInvRecord(res);

				list.add(record);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	private Invoice createInvRecord(ResultSet res) throws SQLException {
		Invoice record = new Invoice();
		record.invNo = res.getInt("invNo");
		record.invDate = res.getDate("invDate");
		record.systemKind = res.getString("systemKind");
		record.docuKind = res.getString("docuKind");
		record.docNo = res.getString("docNo");
		record.docRef = res.getString("docRef");
		record.totalItem = res.getDouble("totalItem");
		record.totalAmt = res.getDouble("totalAmt");
		record.totalAmtText = res.getString("totalAmtText");
		record.totalPaid = res.getDouble("totalPaid");
		record.totalVat = res.getDouble("totalVat");
		// record.totalItem = res.getDouble("totalItem");
		record.contCode = res.getString("contCode");
		record.status = res.getString("status");
		record.contactAddr = res.getString("contactAddr");
		record.creUser = res.getString("creUser");
		record.creTime = res.getTimestamp("creTime");
		record.shipDate = res.getDate("shipDate");
		record.dueDate = res.getDate("dueDate");
		record.shipTo = res.getString("shipTo");
		record.note = res.getString("note");
		record.poNr = res.getString("poNr");
		record.printCounter = res.getInt("printCounter");
		return record;
	}

	@Override
	public Invoice selectInvoice(Integer invNo) {
		String sql = "select * from invoice where comCode='"
				+ super.getSessVar("comCode") + "' and invNo=" + invNo;
		ResultSet res = getDatabase().executeQuery(sql);
		Invoice record = null;
		try {
			if (res.next()) {
				record = createInvRecord(res);
				record.docuKindName = "Temporary";
				if (record.docuKind != null) {
					res = getDatabase().executeQuery(
							"select descript from docukind where comCode='"
									+ getSessVar("comCode") + "' and docCode='"
									+ record.docuKind + "'");
					if (res.next())
						record.docuKindName = res.getString(1);
				}
				sql = "select * from invitem where comCode='"
						+ getSessVar("comCode") + "' and invNo=" + invNo;
				res = getDatabase().executeQuery(sql);

				while (res.next()) {
					InvItem item = new InvItem();
					item.invNo = invNo;
					item.item = res.getInt("itemNo");
					item.proName = res.getString("proName");
					item.unit = res.getString("unit");
					item.proCode = res.getString("proCode");
					item.qty = res.getDouble("qty");
					item.unitPrice = res.getDouble("unitPrice");
					item.stkCode = res.getString("stkCode");
					item.refundPrc = res.getDouble("refundPrc");
					item.refundQty = res.getDouble("refundQty");
					item.refundTotal = res.getDouble("refundTotal");
					item.refundItem = res.getInt("refundItem");
					item.total = res.getDouble("total");
					item.vat = res.getDouble("vat");
					item.vatAmt = res.getDouble("vatAmt");
					item.discount = res.getDouble("discount");
					item.product = selectProduct(item.proCode);
					item.stocks = item.product.stocks;
					record.items.add(item);
				}

				sql = "select payment.*, paymeth.paymentMethod from payment, paymeth where payment.comCode='"
						+ getSessVar("comCode")
						+ "' and payment.comCode=paymeth.comCode and invNo="
						+ invNo + " and payment.payMethod=paymeth.paymethCode";
				res = getDatabase().executeQuery(sql);
				while (res.next()) {
					Payment pay = new Payment();
					pay.invNo = invNo;
					pay.payNo = res.getInt("payNo");
					pay.systemKind = res.getString("systemKind");
					pay.itemKind = res.getString("itemKind");
					pay.docNo = res.getString("docNo");
					pay.payDate = res.getDate("payDate");
					pay.chkCardNo = res.getString("chkCardNo");
					pay.checkDate = res.getDate("chkCardDate");
					pay.amt = res.getDouble("amt");
					pay.payMethod = res.getInt("payMethod");
					pay.payMethodName = res.getString("paymentMethod");
					record.payments.add(pay);
				}
			}
		} catch (SQLException e) {
			log(e.getMessage());
			e.printStackTrace(System.out);
		}
		return record;
	}

	@Override
	public List<InvItem> selectInvItem(Integer invNo) {
		return null;
	}

	@Override
	public boolean deleteInvoice(Integer invNo) {
		int rowEffect = 0;
		try {
			rowEffect = getDatabase().executeUpdate(
					"delete from invoice where comCode='"
							+ super.getSessVar("comCode") + "' and invNo="
							+ invNo);
		} catch (Exception e) {
			log(e.getMessage());
		}
		return rowEffect > 0;
	}

	@Override
	public String updateInvoice(Invoice inv) {
		String x = inv.invNo.toString();
		Database db = getDatabase();
		db.createSQL(
				"updInv",
				"update invoice set invDate=?,contCode=?,totalAmt=?,totalAmtText=?,contactAddr=?,totalVat=?,docRef=?,modUser=?,modTime=NOW() "
						+ "where comCode=? and invNo=?");
		db.createSQL(
				"insInvItem",
				"insert into invitem(comCode,invNo,itemNo,proCode,proName,qty,unitPrice,vat,vatAmt,discount,total,stkCode, creUser, creTime, refundPrc, refundQty, refundTotal, refundItem, unit) "
						+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?,?,?,?)");

		db.createSQL(
				"updInvItem",
				"update invitem set proName=?, qty=?, unitPrice=?,total=?,vat=?,vatAmt=?,discount=?,stkCode=?,modUser=?,modTime=NOW() "
						+ "where comCode=? and invNo=? and itemNo=?");

		if (inv.status.equals("0"))
			try {
				db.executeUpdate("start transaction");
				Object[] params = new Object[10];
				params[0] = inv.invDate;
				params[1] = inv.contCode;
				params[2] = inv.totalAmt;
				params[3] = inv.totalAmtText;
				params[4] = inv.contactAddr;
				params[5] = inv.totalVat;
				params[6] = inv.docRef;
				params[7] = super.getSessVar("user");
				params[8] = super.getSessVar("comCode");
				params[9] = inv.invNo;
				db.execSqlMap("updInv", params);

				db.executeUpdate("delete from invitem where comCode='"
						+ getSessVar("comCode") + "' and invNo=" + inv.invNo);

				Object[] insItem = new Object[18];

				for (InvItem item : inv.items) {
					params = new Object[18];
					insertInvItem(inv.invNo, insItem, item);
				}
				/*
				 * db.executeUpdate("delete from invitem where comCode='" +
				 * getSessVar("comCode") + "' and invNo=" + inv.invNo);
				 * 
				 * params = new Object[12]; params[0] = getSessVar("comCode");
				 * params[1] = inv.invNo; for (InvItem item : inv.items) {
				 * params[2] = item.item; params[3] = item.proCode; params[4] =
				 * item.proName; params[5] = item.qty; params[6] =
				 * item.unitPrice; params[7] = item.vat; params[8] =
				 * item.vatAmt; params[9] = item.discount; params[10] =
				 * item.total; params[11] = item.stkCode;
				 * db.execSqlMap("insInvItem", params); }
				 */

				if (inv.upload == 1)
					insertPayment(inv);
				db.executeUpdate("commit");
			} catch (Exception e) {
				e.printStackTrace(System.out);
				db.executeUpdate("rollback");
			}

		return x;
	}

	private void updateStock(Invoice inv) throws SQLException {
		Database db = getDatabase();
		PreparedStatement stmt = null;
		if (!inv.systemKind.equals("C") && inv.status.equals("0")) {
			if (inv.docRef == null || inv.docRef.equals("")) {
				String sql = "select runNo from docukind where comCode='"
						+ getSessVar("comCode") + "' and docCode='"
						+ inv.docuKind + "'";
				ResultSet rs = db.executeQuery(sql);
				if (rs.next())
					inv.docRef = formatDocRef(rs.getInt(1));
				sql = "update invoice set status = '1', docRef = '"
						+ inv.docRef + "' where comCode='"
						+ getSessVar("comCode") + "' and invNo=" + inv.invNo;
				db.executeUpdate(sql);

				db.executeUpdate("update docukind set runNo = runNo + 1 where comCode='"
						+ getSessVar("comCode")
						+ "' and docCode='"
						+ inv.docuKind + "'");
			}
			if (inv.systemKind.equals("D"))
				stmt = db
						.getConnection()
						.prepareStatement(
								"update contact set custLastDate=?, custLastDocRef=?, custLastInv=? where comCode='"
										+ this.getSessVar("comCode")
										+ "' and contCode=?");

			if (inv.systemKind.equals("C"))
				stmt = db
						.getConnection()
						.prepareStatement(
								"update contact set suppLastDate=?, suppLastDocRef=?, suppLastInv=? where comCode='"
										+ this.getSessVar("comCode")
										+ "' and contCode=?");
			if (stmt != null) {
				stmt.setObject(1, inv.invDate);
				stmt.setString(2, inv.docRef);
				stmt.setInt(3, inv.invNo);
				stmt.setString(4, inv.contCode);
				stmt.execute();
			}

			try {
				stmt = db
						.getConnection()
						.prepareStatement(
								"insert into stkmovement(comCode,proCode,invNo,fromStock,toStock,qty,creUser,creTime)"
										+ " values (?,?,?,?,?,?,?,now())");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			db.createSQL(
					"updStk",
					"update stock set balance = balance + ?, rbalance = rbalance + ? where comCode=? and proCode=? and stkCode=?");
			double num = 0;
			Object[] stkParams = new Object[5];
			stkParams[2] = getSessVar("comCode");

			for (InvItem item : inv.items) {
				if (item.stkCode != null) {
					num = item.qty;
					if (inv.systemKind.equals("D"))
						num = -num;
					stkParams[0] = num;
					stkParams[1] = num;
					stkParams[3] = item.proCode;
					stkParams[4] = item.stkCode;
					try {
						db.execSqlMap("updStk", stkParams);
					} catch (SQLException e) {
						e.printStackTrace();
					}
					stmt.setString(1, getSessVar("comCode"));
					stmt.setString(2, item.proCode);
					stmt.setInt(3, item.invNo);
					if (num >= 0) {
						stmt.setString(4, null);
						stmt.setString(5, item.stkCode);
					} else {
						stmt.setString(4, item.stkCode);
						stmt.setString(5, null);
					}

					stmt.setDouble(6, item.qty);
					stmt.setString(7, getSessVar("user"));
					stmt.execute();
				}
			}
		}
	}

	private void undoStock(Invoice inv) {
		Database db = getDatabase();
		PreparedStatement stmt = null;
		try {
			stmt = db
					.getConnection()
					.prepareStatement(
							"insert into stkmovement(comCode,proCode,invNo,fromStock,toStock,qty,creUser,creTime)"
									+ " values (?,?,?,?,?,?,?,now())");
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		db.createSQL(
				"updStk",
				"update stock set balance = balance + ?, rbalance = rbalance + ? where comCode=? and proCode=? and stkCode=?");

		if (inv.refundSource != null)
			db.createSQL(
					"undoRefund",
					"update invitem set refundPrc=refundPrc-?, refundQty=refundQty-?,refundTotal=refundTotal-? where comCode=? and invNo=? and itemNo=?");
		double num = 0;
		Object[] stkParams = new Object[5];
		stkParams[2] = getSessVar("comCode");
		for (InvItem item : inv.items) {
			if (item.stkCode != null && item.qty != 0) {
				num = item.qty;
				if (inv.systemKind.equals("C"))
					num = -num;
				stkParams[0] = num;
				stkParams[1] = num;
				stkParams[3] = item.proCode;
				stkParams[4] = item.stkCode;
				try {
					db.execSqlMap("updStk", stkParams);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			System.out.println("*-*-*-*-*-*-*-* " + item.refundItem);
			if (item.refundItem != null) {
				Object[] params = new Object[6];
				params[1] = item.unitPrice;
				params[2] = item.qty;
				params[3] = item.total;
				params[4] = getSessVar("comCode");
				params[5] = inv.refundSource;
				params[6] = item.refundItem;
				try {
					System.out.println("undo refund " + inv.refundSource + ":"
							+ item.refundItem);
					db.execSqlMap("undoRefund", params);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			try {
				stmt.setString(1, getSessVar("comCode"));
				stmt.setString(2, item.proCode);
				stmt.setInt(3, item.invNo);
				if (num >= 0) {
					stmt.setString(4, null);
					stmt.setString(5, item.stkCode);
				} else {
					stmt.setString(4, item.stkCode);
					stmt.setString(5, null);
				}
				stmt.setDouble(6, item.qty);
				stmt.setString(7, getSessVar("user"));
				stmt.execute();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public List<Payment> selectPayment(Integer invNo) {
		List<Payment> output = new ArrayList<Payment>();
		String sql = "select * from payment where comCode='"
				+ super.getSessVar("comCode") + "' and invNo=" + invNo;
		ResultSet res = getDatabase().executeQuery(sql);
		Payment payment;
		try {
			while (res.next()) {
				payment = new Payment();
				payment.payNo = res.getInt("payNo");
				payment.systemKind = res.getString("systemKind");
				payment.itemKind = res.getString("itemKind");
				payment.invNo = res.getInt("invNo");
				payment.docNo = res.getString("docNo");
				payment.payDate = res.getDate("payDate");
				payment.contCode = res.getString("contCode");
				payment.payRef = res.getString("payRef");
				payment.payMethod = res.getInt("payMethod");
				payment.amt = res.getDouble("amt");
				payment.clearAmt = res.getDouble("clearAmt");
				payment.ledgerAcc = res.getString("ledgerAcc");
				payment.voucherNo = res.getInt("voucherNo");
				payment.bkCode = res.getString("bkCode");
				payment.accountNo = res.getString("accountNo");
				payment.chkCardNo = res.getString("chkCardNo");
				payment.bankCardName = res.getString("bankCardName");
				payment.checkDate = res.getDate("chkCardDate");
				payment.cardExpDate = res.getDate("cardExpDate");
				payment.cardOwner = res.getString("cardOwner");
				payment.bookingRun = res.getInt("bookingRuN");
				payment.clearingDate = res.getDate("clearingDate");
				payment.note = res.getString("note");
				output.add(payment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return output;
	}

	@Override
	public Payment selectPayment(String payNo) {
		return null;
	}

	@Override
	public boolean insertPayment(Payment record) {
		int rowEffect = 0;
		Database db = getDatabase();
		Object[] params = null;
		boolean overLimit = false;
		if (record != null) {

			try {
				db.executeUpdate("start transaction");

				Invoice inv = this.selectInvoice(record.invNo);
				if (inv.status.equals("0")) {
					ResultSet rs = db
							.executeQuery("select * from contact where comCode='"
									+ getSessVar("comCode")
									+ "' and contCode='" + inv.contCode + "'");
					if (rs.next()) {
						double limit = 0;
						if (inv.systemKind.equals("D")) {
							limit = rs.getDouble("custLimit");
						} else if (inv.systemKind.equals("C")) {
							limit = rs.getDouble("suppLimit");
						}
						if (limit >= 1) {
							if (limit < record.amt) {
								overLimit = true;
								throw new SQLException("Over Dept Limit");
							} else {
								if (inv.systemKind.equals("D")) {
									db.executeUpdate("update contact set custBalance=custBalance+"
											+ inv.totalAmt
											+ " where comCode='"
											+ getSessVar("comCode")
											+ "' and contCode='"
											+ inv.contCode
											+ "'");
								} else if (inv.systemKind.equals("C")) {
									db.executeUpdate("update contact set suppBalance=suppBalance+"
											+ inv.totalAmt
											+ " where comCode='"
											+ getSessVar("comCode")
											+ "' and contCode='"
											+ inv.contCode
											+ "'");
								}
							}
						}
					}
					if (!overLimit) {
						db.createSQL("updatePaid",
								"update invoice set status = '1', docuKind = ? where invNo = ? and comCode='"
										+ getSessVar("comCode") + "'");
						// เปลี่ยน DocuKind เมื่อ เป็นบิลเงินเชื่อ record.amt =
						// 0
						params = new Object[2];
						inv.docuKind = "I";
						if (record.contCode.equals("999999")) {
							inv.docuKind = "H";
						}
						if (record.amt == 0) {
							inv.docuKind = "C";
						}
						params[0] = inv.docuKind;
						params[1] = record.invNo;
						rowEffect = db.execSqlMap("updatePaid", params);
						this.updateStock(inv);

					}

				}

				if (record.amt != 0) {
					if (record.systemKind.equals("D")) {
						db.executeUpdate("update contact set custBalance=custBalance-"
								+ record.amt
								+ " where comCode='"
								+ getSessVar("comCode")
								+ "' and contCode='"
								+ record.contCode + "'");
					} else if (record.systemKind.equals("C")) {
						db.executeUpdate("update contact set suppBalance=suppBalance-"
								+ record.amt
								+ " where comCode='"
								+ getSessVar("comCode")
								+ "' and contCode='"
								+ record.contCode + "'");
					}

					ResultSet rs = db
							.executeQuery("select paymentNr from company where comCode='"
									+ getSessVar("comCode") + "'");
					if (rs.next()) {
						record.payNo = rs.getInt(1);
						if (record.payNo == null)
							record.payNo = 1;
						else
							record.payNo++;
					}
					db.executeUpdate("update company set paymentNr="
							+ record.payNo + " where comCode='"
							+ getSessVar("comCode") + "'");

					rs = db.executeQuery("SELECT ledgerAcc FROM windmill.itemkind where comCode = '"
							+ getSessVar("comCode")
							+ "' and systemKind = '"
							+ record.systemKind
							+ "' and itemkCode = '"
							+ record.itemKind + "'");
					if (rs.next()) {
						record.ledgerAcc = rs.getString(1);
					}

					if (record.payMethod == 2)
						record.clearAmt = 0.0;
					else
						record.clearAmt = record.amt;

					db.createSQL(
							"insPayment",
							"insert into payment values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,NOW(), ?,NOW(),?)");
					params = new Object[26];
					params[0] = this.getSessVar("comCode");
					params[1] = record.payNo;
					params[2] = record.systemKind;
					params[3] = record.itemKind;
					params[4] = record.invNo;
					params[5] = record.docNo;
					// params[6] = record.payDate;
					params[6] = new Date();
					params[7] = record.contCode;
					params[8] = record.payRef;
					params[9] = record.payMethod;
					params[10] = record.amt;
					params[11] = record.clearAmt;
					params[12] = record.ledgerAcc;
					params[13] = record.voucherNo;
					params[14] = record.bkCode;
					params[15] = record.accountNo;
					params[16] = record.chkCardNo;
					params[17] = record.bankCardName;
					params[18] = record.checkDate;
					params[19] = record.cardExpDate;
					params[20] = record.cardOwner;
					params[21] = record.bookingRun;
					params[22] = record.clearingDate;
					params[23] = getSessVar("user");
					params[24] = getSessVar("user");
					params[25] = record.note;
					rowEffect = db.execSqlMap("insPayment", params);

					db.executeUpdate("update invoice set totalPaid=totalPaid+"
							+ record.amt + " where comCode='"
							+ getSessVar("comCode") + "' and invNo="
							+ record.invNo);
				}

				db.executeUpdate("commit");
			} catch (SQLException e) {
				log(e.getMessage());
				getDatabase().executeUpdate("rollback");
			}
		}
		return rowEffect > 0 && !overLimit;
	}

	@Override
	public boolean updatePayment(Payment record) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean deletePayment(String payNo) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public List<String[]> selectPaymeth() {
		String[] paymeth;
		List<String[]> list = new ArrayList<String[]>();
		ResultSet rs;
		try {
			rs = getDatabase().executeQuery(
					"select * from paymeth where comCode='"
							+ getSessVar("comCode") + "'");
			while (rs.next()) {
				paymeth = new String[4];
				paymeth[0] = rs.getString("paymethCode");
				paymeth[1] = rs.getString("paymentMethod");
				paymeth[2] = rs.getString("paymentHandlingType");
				paymeth[3] = rs.getString("ledgerAcc");
				list.add(paymeth);
			}
		} catch (Exception e) {
			log(e.getMessage());
		}
		return list;
	}

	@Override
	public List<ContDept> selectContDept(String contCode) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean insertContDept(ContDept record) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean updateContDept(ContDept record) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean deleteContDept(String contCode) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean insertPaymeth(Paymeth record) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean updatePaymeth(Paymeth record) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean deletePaymeth(Paymeth contCode) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public List<Paymeth> selectPaymeth(String payCode) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String refundInvoice(Invoice record) {
		Database db = this.getDatabase();
		String invNo = null;
		Invoice refund = new Invoice();
		refund.invDate = new Date();
		refund.systemKind = record.systemKind;

		refund.contCode = record.contCode;
		refund.contactAddr = record.contactAddr;
		refund.projCode = record.projCode;
		refund.status = "0";
		refund.totalItem = record.totalItem;
		refund.totalAmt = record.totalAmt;
		refund.totalAmtText = record.totalAmtText;
		refund.totalPaid = record.totalPaid;
		refund.totalVat = record.totalVat;
		refund.upload = 1; // มีการชำระเงิน
		refund.invDate = new Date();
		refund.refundCode = record.refundCode;
		refund.refundSource = record.invNo;
		refund.items = new ArrayList<InvItem>();
		for (InvItem item : record.items) {
			// System.out.println(item.proCode + " : " + item.edited + " : " +
			// item.tmpRefundPrc);
			// if (item.refundPrc != item.unitPrice || (item.tmpRefundQty > 0)
			// || (item.tmpTotal != item.total)) {
			if (item.edited) {
				InvItem it = item.clone();
				it.refundItem = it.item;
				System.out.println("price = " + it.unitPrice + " : "
						+ it.tmpDiscount);
				System.out.println("vat = " + it.vatAmt + " : " + it.tmpVatAmt);
				System.out.println("total = " + it.total + " : "
						+ it.tmpRefundTotal);
				System.out.println("********************");
				if (it.tmpRefundQty != 0) {
					it.qty = it.tmpRefundQty;
					it.tmpDiscount = 0.0;
					it.total = it.tmpRefundTotal;
					// it.vatAmt = it.tmpVatAmt;

					// it.refundQty += it.tmpRefundQty;
					// item.refundQty += item.tmpRefundQty;
				} else if (item.tmpDiscount > 0) {
					// it.unitPrice = it.tmpDiscount - it.unitPrice;
					// it.tmpDiscount = it.unitPrice;
					it.refundPrc = 100 - (100 - it.tmpDiscount);
					it.total = it.tmpRefundTotal - it.total;
					// it.vatAmt = it.tmpVatAmt - it.vatAmt;
					it.tmpRefundTotal = it.total;
				} else {
					it.total = it.tmpRefundTotal - it.total;
					it.tmpRefundTotal = it.total;
					// it.vatAmt = it.tmpVatAmt - it.vatAmt;
					// it.tmpDiscount = (it.total - it.vatAmt) / it.qty;
					// item.unitPrice = 0.0;
				}
				it.vatAmt = it.total * it.vat;
				System.out.println("price = " + it.unitPrice + " : "
						+ it.tmpDiscount);
				System.out.println("vat = " + it.vatAmt + " : " + it.tmpVatAmt);
				System.out.println("total = " + it.total + " : "
						+ it.tmpRefundTotal);
				refund.items.add(it);
				db.executeUpdate("update invitem set refundQty=refundQty+"
						+ it.tmpRefundQty + ", refundPrc=100-((100-refundPrc)*"
						+ (100 - it.tmpDiscount) / 100
						+ "), refundTotal=refundTotal+" + it.tmpRefundTotal
						+ " where comCode='" + super.getSessVar("comCode")
						+ "' and itemNo=" + item.item);
			}
		}

		refund.payments = record.payments;

		invNo = insertInvoice(refund);
		return invNo;
	}

	@Override
	public RReason isStockUpdate(String resCode) {
		Database db = getDatabase();
		ResultSet s = db
				.executeQuery("select resCode, descript, stockUpdate from rreason where comCode='"
						+ super.getSessVar("comCode")
						+ "' and resCode='"
						+ resCode + "'");
		RReason x = new RReason();
		try {
			if (s.next())
				x.resCode = s.getInt(1);
			x.descript = s.getString(2);
			x.stockUpdate = s.getBoolean(3);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return x;
	}

	@Override
	public List<ContDept> selectAddrDept(String contCode) {
		Database db = this.getDatabase();
		String sql = "select * from contdept where comCode='"
				+ this.getSessVar("comCode") + "' and contCode='" + contCode
				+ "'";
		System.out.println(sql);
		ResultSet rs = db.executeQuery(sql);
		List<ContDept> addrDept = new ArrayList<ContDept>();
		try {
			while (rs.next()) {
				ContDept record = new ContDept();
				record.contCode = contCode;
				record.prefix = rs.getString("prefix");
				record.name = rs.getString("name");
				record.surname = rs.getString("surname");
				record.dept = rs.getString("dept");
				record.phone = rs.getString("phone");
				record.email = rs.getString("email");
				addrDept.add(record);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return addrDept;
	}

	@Override
	public boolean saveAddrDept(List<ContDept> list) {
		Database db = this.getDatabase();

		return true;
	}

	@Override
	public boolean insertPayment(Invoice inv) {
		int rowEffect = 0;
		Database db = getDatabase();
		Object[] params = null;
		boolean overLimit = false;
		try {
			db.executeUpdate("start transaction");
			// Invoice inv = this.selectInvoice(invNo);
			double bal = inv.totalAmt - inv.totalPaid;
			if (inv.payments.size() > 0) {
				ResultSet rs = null;
				double paid = 0.0;
				double payAmt = 0.0;
				for (Payment record : inv.payments) {
					System.out.println(record.amt);
					// record.invNo = inv.invNo;
					if (record.payMethod != 0 && record.payMethod != 2) {
						paid += record.amt;
					}
					payAmt += record.amt;
					rs = db.executeQuery("select paymentNr from company where comCode='"
							+ getSessVar("comCode") + "'");
					if (rs.next()) {
						record.payNo = rs.getInt(1);
						if (record.payNo == null)
							record.payNo = 1;
						else
							record.payNo++;
					}

					db.executeUpdate("update company set paymentNr=paymentNr+1"
							+ " where comCode='" + getSessVar("comCode") + "'");

					record.itemKind = "P";
					if (record.payMethod == 0)
						record.itemKind = "D";

					if (!record.itemKind.equals("P")) {
						rs = db.executeQuery("SELECT ledgerAcc FROM windmill.itemkind where comCode = '"
								+ getSessVar("comCode")
								+ "' and systemKind = '"
								+ record.systemKind
								+ "' and itemkCode = '" + record.itemKind + "'");
					} else {
						rs = db.executeQuery("SELECT ledgerAcc FROM windmill.paymeth where comCode = '"
								+ getSessVar("comCode")
								+ "' and paymethCode = " + record.payMethod);
					}

					if (rs.next()) {
						record.ledgerAcc = rs.getString(1);
					}

					if (record.payMethod == 2)
						record.clearAmt = 0.0;
					else
						record.clearAmt = record.amt;
					record.invNo = inv.invNo;
					record.systemKind = inv.systemKind;
					db.createSQL(
							"insPayment",
							"insert into payment values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,NOW(), ?,NOW(),?)");
					params = new Object[26];
					params[0] = this.getSessVar("comCode");
					params[1] = record.payNo;
					params[2] = record.systemKind;
					params[3] = record.itemKind;
					params[4] = record.invNo;
					params[5] = record.docNo;
					params[6] = record.payDate;
					params[7] = record.contCode;
					params[8] = record.payRef;
					params[9] = record.payMethod;
					params[10] = record.amt;
					params[11] = record.clearAmt;
					params[12] = record.ledgerAcc;
					params[13] = record.voucherNo;
					params[14] = record.bkCode;
					params[15] = record.accountNo;
					params[16] = record.chkCardNo;
					params[17] = record.bankCardName;
					params[18] = record.checkDate;
					params[19] = record.cardExpDate;
					params[20] = record.cardOwner;
					params[21] = record.bookingRun;
					params[22] = record.clearingDate;
					params[23] = getSessVar("user");
					params[24] = getSessVar("user");
					params[25] = record.note;
					rowEffect = db.execSqlMap("insPayment", params);
				}
				bal = inv.totalAmt - payAmt;
				System.out.println("bal = " + inv.totalAmt + " - " + payAmt);
				// if (bal != 0) {
				// Payment p = new Payment();
				// p.systemKind = inv.systemKind;
				// p.invNo = inv.invNo;
				// p.amt = bal;
				// p.payMethod = 0;
				// p.payDate = inv.invDate;
				// inv.payments.add(p);
				// }
				if (bal == 0) {
					db.createSQL("updatePaid",
							"update invoice set docuKind = ?, totalPaid = ? where invNo = ? and comCode='"
									+ getSessVar("comCode") + "'");
					int service = 0;
					if (inv.docuKind != null && inv.docuKind.equals("S"))
						service = 1;
					if (inv.docuKind != null && inv.docuKind.equals("T"))
						service = 1;
					// เปลี่ยน DocuKind เมื่อ เป็นบิลเงินเชื่อ record.amt =
					// 0
					params = new Object[3];
					// if (inv.docuKind == null || inv.docuKind.equals("")) {
					if (inv.refundSource == null) {
						if (inv.contCode.equals("999999")) { // บิลเงินสดกำกับภาษีอย่างย่อ
							inv.docuKind = "H";
						} else
							inv.docuKind = "I";
					} else
						inv.docuKind = "R";

					bal = inv.totalAmt - inv.totalPaid;
					if (bal != 0) { // บิลเงินเชื่อ
						if (inv.refundSource == null)
							inv.docuKind = "C";
						else
							inv.docuKind = "Q";
					}

					// งานบิลบริการ เช็คจาก company ว่า service == 1

					// rs =
					// db.executeQuery("select service from company where comCode='"
					// + getSessVar("comCode") + "'");

					// if (rs.next())
					// service = rs.getInt(1);
					if (service == 1) {
						if (inv.docuKind.equals("C")
								|| inv.docuKind.equals("H")
								|| inv.docuKind.equals("I"))
							inv.docuKind = "S";
						else if (inv.docuKind.equals("R")
								|| inv.docuKind.equals("Q"))
							inv.docuKind = "T";
					}

					// System.out.println("docuKind = " + inv.docuKind);
					// if (record.docuKind.equals("S"))
					// refund.dodv.totalPaid;
					params[0] = inv.docuKind;
					params[1] = inv.totalPaid;
					params[2] = inv.invNo;
					rowEffect = db.execSqlMap("updatePaid", params);
					db.executeUpdate("update contact set custBalance=custBalance+"
							+ (inv.totalAmt - paid)
							+ " where comCode='"
							+ getSessVar("comCode")
							+ "' and contCode='"
							+ inv.contCode + "'");
					// inv.status = "1";
					inv.docRef = null;
					if (service == 0)
						this.updateStock(inv);
				}
			}
			db.executeUpdate("commit");
		} catch (SQLException e) {
			log(e.getMessage());
			getDatabase().executeUpdate("rollback");
		}

		return rowEffect > 0 && !overLimit;
	}

	private Product selectProduct(String id) {
		String sql = "select * from product where comCode='"
				+ getSessVar("comCode") + "' and proCode='" + id + "'";
		Product record = null;
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				record = new Product();
				record.proCode = res.getString("proCode");
				record.name = res.getString("name");
				record.descript = res.getString("descript");
				record.proGrp = res.getString("proGrp");
				record.unit = res.getString("unit");
				record.discount = res.getDouble("discount");
				record.sellPrice = res.getDouble("sellPrice");
				record.purPrice = res.getDouble("purPrice");
				record.purVAT = res.getString("purVAT");
				record.sellVAT = res.getString("sellVAT");
				record.minPrice = res.getDouble("minPrice");
				record.incomeAcc = res.getString("incomeAcc");
				record.costAcc = res.getString("costAcc");
				record.stockItem = res.getBoolean("stockItem");
				record.sellItem = res.getBoolean("sellItem");
				record.buyItem = res.getBoolean("buyItem");
				record.active = res.getBoolean("active");
				record.creUser = res.getString("creUser");
				record.creTime = res.getString("creTime");
				record.stocks = this.selectStock(record.proCode);
				record.dc1 = res.getDouble("dc1");
				record.dc2 = res.getDouble("dc2");
				record.dc3 = res.getDouble("dc3");
				record.dc4 = res.getDouble("dc4");
				record.dc5 = res.getDouble("dc5");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return record;
	}

	private List<Stock> selectStock(String proCode) {
		String sql = "select * from stock where comCode='"
				+ getSessVar("comCode") + "' and proCode='" + proCode
				+ "' order by priority desc, stkCode";
		Stock record = null;
		List<Stock> list = new ArrayList<Stock>();
		try {
			ResultSet res = getDatabase().executeQuery(sql);
			while (res.next()) {
				record = new Stock();
				record.proCode = res.getString("proCode");
				record.stkCode = res.getString("stkCode");
				record.balance = res.getInt("balance");
				record.rbalance = res.getInt("rbalance");
				record.minQty = res.getInt("minQty");
				record.orderQty = res.getInt("orderQty");
				record.priority = res.getInt("priority");
				list.add(record);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean rollbackInvoice(Integer invNo) {
		Database db = getDatabase();
		boolean result = false;
		Invoice inv = selectInvoice(invNo);
		try {
			if (inv.status.compareTo("0") > 0) {
				db.executeUpdate("start transaction");
				undoStock(inv);

				if (inv.systemKind.equals("D") && inv.docuKind.equals("R")) {

				}
				String sql = "delete from payment where comCode='"
						+ getSessVar("comCode") + "' and invNo=" + invNo;
				db.executeUpdate(sql);
				sql = "update invoice set status=0, totalPaid=0, printCounter=0 where comCode='"
						+ getSessVar("comCode") + "' and invNo=" + invNo;
				db.executeUpdate(sql);
				db.executeUpdate("commit");
			}
			result = true;
		} catch (Exception e) {
			log(e.getMessage());
			getDatabase().executeUpdate("rollback");
		}
		return result;
	}

	@Override
	public Integer find(String docuKind, String docRef) {
		Integer invNo = -1;
		Database db = getDatabase();
		ResultSet rs = db
				.executeQuery("select invNo from invoice where comCode='"
						+ getSessVar("comCode") + "' and docuKind='" + docuKind
						+ "' and docRef='" + docRef + "'");
		try {
			if (rs.next())
				invNo = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return invNo;
	}
}
